*******************************************************************************************
*******************************************************************************************
******************** Reclassification Risk in the Small Group Health Insurance Market
******************* by Sebastian Fleitas, Gautam Gowrisankaran and Anthony Lo Sasso 
*******************************************************************************************
*******************************************************************************************
*******************************************************************************************
******************** Table B1 Panel B
*******************************************************************************************
*******************************************************************************************
*******************************************************************************************

cd "/Users/sebastianfleitas/Dropbox/ReclassificationRisk/dataset_revisions"
use database_firm_level.dta, replace 

*** descriptive statistics sample firm level
gen in2015_aux = (year==2015)
bys customer_number: egen in2015 = max(in2015_aux)
gen in2014_aux = (year==2014)
bys customer_number: egen in2014 = max(in2014_aux)
gen in2013_aux = (year==2013) 
bys customer_number: egen in2013 = max(in2013_aux)
gen v2013_2014 = 1 if in2013==1 & in2014==1 & in2015!=1
gen v2014_2015 = 1 if in2013!=1 & in2014==1 & in2015==1
gen v2013_2014_2015 = 1 if in2013==1 & in2014==1 & in2015==1
gen v2013_2015 = 1 if in2013==1 & in2014!=1 & in2015==1

xtset firm year

  foreach Y in par2 mean_employee mean_spouse mean_children mean_others  mean_female  lagged_chronic_cancer lagged_chronic_ami lagged_chronic_transplant lagged_chronic_diabetes lagged_code_hypertension lagged_code_heartfailure  lagged_code_asthma {
  gen `Y'_100 = `Y'*100
  }
gen chronickidneydisease_100 = lagged_code_chronickidneydisease*100


set more off 
gen mean_premium2013 = mean_premium if year==2013
gen mean_premium2014 = mean_premium if year==2014
gen mean_premium2015 = mean_premium if year==2015


qui {
  *complete below with all variables 
  foreach X in numpeople par2_100 mean_employee_100 mean_spouse_100 mean_children_100 mean_others_100 mean_age mean_female_100 mean_paid mean_outpocket mean_allowed_sum mean_premium mean_premium2013 mean_premium2014 mean_premium2015 mean_pred_riskscore D.mean_pred_riskscore mean_pred_riskscore_rp D.mean_pred_riskscore_rp lagged_chronic_cancer_100 lagged_chronic_ami_100 lagged_chronic_transplant_100 lagged_chronic_diabetes_100 lagged_code_hypertension_100 lagged_code_heartfailure_100 chronickidneydisease_100 lagged_code_asthma_100 {

    /*
	if "`X'"=="numpeople" | "`X'"=="par2_100" {
	if "`X'"=="numpeople" noi dis _con "Subscribers"
    if "`X'"=="par2_100"   noi dis _con "Take up rate (\%)"
	sum `X' 
    local mean1 = r(mean)
    local sd1= r(sd)
    local val11=string(`mean1',"%8.0f")
	local val21=string(`sd1',"%8.0f")
    noi dis _con "& $`val11'$ ($`val21'$) "
	sum `X' if v2013_2014_2015==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0f")
	local val22=string(`sd2',"%8.0f")
    noi dis _con "& $`val12'$ ($`val22'$)  "
	sum `X' if v2014_2015==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0f")
	local val22=string(`sd2',"%8.0f")
    noi dis _con "& $`val12'$ ($`val22'$)  "
	sum `X' if v2013_2014==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0f")
	local val22=string(`sd2',"%8.0f")
    noi dis "& $`val12'$ ($`val22'$) \\ "
	}
	
	
	else if "`X'"=="mean_employee_100" {
	noi dis  "Relation ($\%$) & & & & \\"
	if "`X'"=="mean_employee_100" noi dis _con "\hspace{0.1in} Employees"
	sum `X' 
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2013_2014_2015==1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2014_2015==1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2013_2014==1
    local mean2 = r(mean)
    local val12=string(`mean2',"%8.2f")
    noi dis "& $`val12'$  \\ "
	}
	*/
	
	
	else if "`X'"=="mean_spouse_100" | "`X'"=="mean_children_100" | "`X'"=="mean_others_100" {
	if "`X'"=="mean_spouse_100" noi dis _con "\hspace{0.1in} Spouses"
	if "`X'"=="mean_children_100" noi dis _con "\hspace{0.1in} Children"
	if "`X'"=="mean_others_100" noi dis _con "\hspace{0.1in} Others"
	sum `X' 
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2013_2014_2015==1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2014_2015==1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if  v2013_2014==1
    local mean2 = r(mean)
    local val12=string(`mean2',"%8.2f")
    noi dis "& $`val12'$  \\ "
	}
	
	
	/*
	
	else if "`X'"=="mean_age"  {
	if "`X'"=="mean_age" noi dis _con "Age"
	sum `X' 
    local mean1 = r(mean)
    local sd1= r(sd)
    local val11=string(`mean1',"%8.0f")
	local val21=string(`sd1',"%8.0f")
    noi dis _con "& $`val11'$ ($`val21'$) "
	sum `X' if v2013_2014_2015==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0f")
	local val22=string(`sd2',"%8.0f")
    noi dis _con "& $`val12'$ ($`val22'$)  "
	sum `X' if v2014_2015==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0f")
	local val22=string(`sd2',"%8.0f")
    noi dis _con "& $`val12'$ ($`val22'$)  "
	sum `X' if v2013_2014==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0f")
	local val22=string(`sd2',"%8.0f")
    noi dis "& $`val12'$ ($`val22'$) \\ "
	}
	
	
	else if "`X'"=="mean_female_100" {
	if "`X'"=="mean_female_100" noi dis _con "Female (\%)"
	sum `X' 
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.0f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2013_2014_2015==1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.0f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2014_2015==1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.0f")
    noi dis _con "& $`val11'$ "
	sum `X' if  v2013_2014==1
    local mean2 = r(mean)
    local val12=string(`mean2',"%8.0f")
    noi dis "& $`val12'$  \\ \hline "
	}

	
	else if "`X'"=="mean_paid" | "`X'"=="mean_outpocket" | "`X'"=="mean_premium" | "`X'"=="mean_premium2013" | "`X'"=="mean_premium2014" | "`X'"=="mean_premium2015" 	{
	if "`X'"=="mean_paid" noi dis  "In dollars: & & & & \\"
	if "`X'"=="mean_paid" noi dis _con "Lagged paid total claims "
	if "`X'"=="mean_outpocket" noi dis _con "Lagged out-of-pocket claims "
	if "`X'"=="mean_premium" noi dis _con "Mean annual premiums "
	if "`X'"=="mean_premium2013" noi dis _con "\hspace{0.1in} 2013"
	if "`X'"=="mean_premium2014" noi dis _con "\hspace{0.1in} 2014"
	if "`X'"=="mean_premium2015" noi dis _con "\hspace{0.1in} 2015"
	sum `X' 
    local mean1 = r(mean)
    local sd1= r(sd)
    local val11=string(`mean1',"%8.0fc")
	local val21=string(`sd1',"%8.0fc")
    noi dis _con "& `val11' (`val21') "
	sum `X' if v2013_2014_2015==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0fc")
	local val22=string(`sd2',"%8.0fc")
    noi dis _con "& `val12' (`val22')  "
	sum `X' if v2014_2015==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0fc")
	local val22=string(`sd2',"%8.0fc")
    noi dis _con "& `val12' (`val22')  "
	sum `X' if v2013_2014==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.0fc")
	local val22=string(`sd2',"%8.0fc")
    noi dis "& `val12' (`val22') \\   "
	}
	
	else if "`X'"=="mean_pred_riskscore" | "`X'"=="D.mean_pred_riskscore" | "`X'"=="mean_pred_riskscore_rp" | "`X'"=="D.mean_pred_riskscore_rp"	{
	if "`X'"=="mean_pred_riskscore" noi dis _con " \hline $ R $"
	if "`X'"=="D.mean_pred_riskscore" noi dis _con " $\Delta R$"
	if "`X'"=="mean_pred_riskscore_rp" noi dis _con "$ R^p $"
	if "`X'"=="D.mean_pred_riskscore_rp" noi dis _con "$\Delta R^p$"
	sum `X' 
    local mean1 = r(mean)
    local sd1= r(sd)
    local val11=string(`mean1',"%8.2fc")
	local val21=string(`sd1',"%8.2fc")
    noi dis _con "& $`val11'$ ($`val21'$) "
	sum `X' if v2013_2014_2015==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.2fc")
	local val22=string(`sd2',"%8.2fc")
    noi dis _con "& $`val12'$ ($`val22'$)  "
	sum `X' if v2014_2015==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.2fc")
	local val22=string(`sd2',"%8.2fc")
    noi dis _con "& $`val12'$ ($`val22'$)  "
	sum `X' if v2013_2014==1
    local mean2 = r(mean)
    local sd2= r(sd)
    local val12=string(`mean2',"%8.2fc")
	local val22=string(`sd2',"%8.2fc")
    noi dis "& $`val12'$ ($`val22'$) \\   "
	}

	*/
	
	
	else if "`X'"=="lagged_chronic_cancer_100"  { 
	if "`X'"=="lagged_chronic_cancer_100" noi dis  "\hline Conditions ($\%$) & & & & \\"
	if "`X'"=="lagged_chronic_cancer_100" noi dis _con "Cancer"
	sum `X' 
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2013_2014_2015==1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2014_2015==1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if  v2013_2014==1
    local mean2 = r(mean)
    local val12=string(`mean2',"%8.2f")
    noi dis "& $`val12'$  \\ "
	}
	
	
	else if "`X'"=="lagged_chronic_ami_100" | "`X'"=="lagged_chronic_transplant_100" | "`X'"=="lagged_chronic_diabetes_100" | "`X'"=="lagged_code_hypertension_100" | "`X'"=="lagged_code_heartfailure_100" | "`X'"=="chronickidneydisease_100" |  "`X'"=="lagged_code_asthma_100" { 
	if "`X'"=="lagged_chronic_ami_100" noi dis _con "Acute myocardial infarction"
	if "`X'"=="lagged_chronic_transplant_100" noi dis _con "Transplant"
	if "`X'"=="lagged_chronic_diabetes_100" noi dis _con "Diabetes"
	if "`X'"=="lagged_code_hypertension_100" noi dis _con "Hypertension"
	if "`X'"=="lagged_code_heartfailure_100" noi dis _con "Heart disease"
	if "`X'"=="chronickidneydisease_100" noi dis _con "Chronic kidney disease"
	if "`X'"=="lagged_code_asthma_100" noi dis _con "Asthma"
	sum `X' 
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2013_2014_2015==1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if v2014_2015==1
	local mean1 = r(mean)
    local val11=string(`mean1',"%8.2f")
    noi dis _con "& $`val11'$ "
	sum `X' if  v2013_2014==1
    local mean2 = r(mean)
    local val12=string(`mean2',"%8.2f")
    noi dis "& $`val12'$  \\ "
	}
	
	
	
  }
} 

bys customer_number          : egen new_mean_premium2012        = mean(auxpremium2012)
bys customer_number          : egen new_mean_premium2013        = mean(mean_premium2013)
bys customer_number          : egen new_mean_premium2014        = mean(mean_premium2014)
bys customer_number          : egen new_mean_premium2015        = mean(mean_premium2015)

gen difprem2013 = new_mean_premium2013 - new_mean_premium2012
gen difprem2014 = new_mean_premium2014 - new_mean_premium2013
gen difprem2015 = new_mean_premium2015 - new_mean_premium2014

gen difprem = difprem2013 if year==2013
replace difprem = difprem2014 if year==2014
replace difprem = difprem2015 if year==2015
sum difprem
sum difprem if v2013_2014_2015==1
sum difprem if v2014_2015==1
sum difprem if v2013_2014==1
